{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ec9dc98c-972d-4bc3-846d-407339f1385b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from scipy.optimize import minimize"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3df9f1f-748a-4798-adac-d40ccdb31fe5",
   "metadata": {},
   "source": [
    "## 开始优化\n",
    "现在有数据：<br />\n",
    "1. 沪深300指数数据：df300\n",
    "2. 沪深300指数近3年每天的数据：df300_daily\n",
    "3. 成分股近3年每天的数据：df\n",
    "4. 成分股近3年涨跌幅时序数据：df1\n",
    "5. 指数权重前20支股票的代码与权重值：df20\n",
    "<br />\n",
    "\n",
    "目标是最小化绝对误差，找到一组不超过20支股票买入的权重w"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2933e5dd-0e01-48ee-a963-b029f53278c5",
   "metadata": {},
   "source": [
    "## 导入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "248596a6-c6fc-4c14-afdd-f1b05d3e22c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "H:\\anaconda3\\Lib\\site-packages\\openpyxl\\styles\\stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    }
   ],
   "source": [
    "# 指数权重前20的成分股近3年每天的数据\n",
    "df=pd.read_excel(\"df_all_top_20.xlsx\",index_col=0)\n",
    "# 指数权重前20的成分股近3年涨跌幅时序数据\n",
    "df1=pd.pivot_table(df,index=[\"trade_date\",\"ts_code\"],values=[\"pct_chg\"])\n",
    "# 沪深300指数基本数据\n",
    "df300=pd.read_excel(\"df_300_w.xlsx\",index_col=0)\n",
    "df20=df300.loc[:19,:] # 指数权重前20的成分股基本数据\n",
    "# 沪深300指数近3年每天的数据\n",
    "df300_daily=pd.read_excel('沪深300指数近3年.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a2ac20ab-43e4-4d84-a8d8-814238b1d84f",
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_code_20=df20['ts_code'].tolist()\n",
    "chg300=df300_daily[['日期Date','涨跌幅(%)Change(%)']]\n",
    "index2codes=ts_code_20\n",
    "chgi=df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ff0a2fe-cd30-4c31-9d55-776fef00074d",
   "metadata": {},
   "source": [
    "## 定义目标函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "71c20fb4-7dd4-4b22-b208-9aa3a265b124",
   "metadata": {},
   "outputs": [],
   "source": [
    "def objective(x):\n",
    "    # 最小化涨跌幅绝对误差之和\n",
    "    days=len(chg300)\n",
    "    abs_error=0\n",
    "    for i in range(days):\n",
    "        # 计算20支股票组合的当日涨跌幅\n",
    "        chg_i=0\n",
    "        date=chg300.loc[i,'日期Date']\n",
    "        str_query='trade_date == ['+ str(date)+']'\n",
    "        df_i=chgi.query(str_query)\n",
    "        df_i=df_i.reset_index()\n",
    "        for j in range(len(x)):\n",
    "            # 根据索引找到股票代码，然后找到当日chg\n",
    "            item=df_i[df_i['ts_code']==index2codes[j]]\n",
    "            if not item.empty:\n",
    "                chg_i_j=np.sum(item['pct_chg']/100)\n",
    "            else:\n",
    "                # 这只股票当天没数据的话就等于0\n",
    "                chg_i_j=0\n",
    "            chg_i+=chg_i_j*x[j]\n",
    "        # 找到当日沪深300的涨跌幅\n",
    "        chg300_i=chg300.loc[i,'涨跌幅(%)Change(%)']/100\n",
    "        # 当日涨跌幅绝对误差(单位：1)\n",
    "        abs_error_i=np.abs(chg300_i-chg_i)\n",
    "        # 求和\n",
    "        abs_error+=abs_error_i\n",
    "    return abs_error"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1edf9e9d-4a57-49e7-9edb-2d27722a6290",
   "metadata": {},
   "source": [
    "## 定义约束条件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0dd5eb79-1d14-4fd6-9e3b-5e5cc756d103",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 定义约束条件\n",
    "def constraint1(x):\n",
    "    return np.sum(x)-1        # 等式约束  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d667705e-a823-4969-b308-ca077303277d",
   "metadata": {},
   "source": [
    "## 给出变量取值范围"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ee1db96e-e7b1-44af-974b-b11052337c8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "nums=20\n",
    "bounds=[]\n",
    "for _ in range(nums):\n",
    "    bounds.append((0.0,1))\n",
    "bounds=tuple(bounds)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2025d23-e4ee-4188-89cc-aa0a331428d4",
   "metadata": {},
   "source": [
    "## 开始优化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e7741cba-4c87-4607-b0e1-17ae008f8d57",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "目标值: 2.0532077849552643\n",
      "最优解为\n",
      "[1.26319738e-01 1.11021969e-01 6.07959963e-02 4.60272301e-02\n",
      " 5.83096994e-02 3.30104101e-02 2.77288026e-02 6.30228247e-02\n",
      " 3.30104101e-02 3.46738804e-02 7.45570318e-02 3.30104101e-02\n",
      " 6.58007534e-02 3.56000395e-18 9.29214484e-03 3.30104101e-02\n",
      " 3.30104101e-02 6.25826060e-02 6.18048631e-02 3.30104101e-02]\n"
     ]
    }
   ],
   "source": [
    "con1 = {'type': 'eq', 'fun': constraint1}\n",
    "cons = ([con1])  \n",
    "\n",
    "x0=np.array([0.05]*20) #定义初始值\n",
    "solution = minimize(objective, x0, method='SLSQP', \\\n",
    "                bounds=bounds, constraints=cons)\n",
    "\n",
    "x = solution.x\n",
    "\n",
    "print('目标值: ' + str(objective(x)))\n",
    "print('最优解为')\n",
    "print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0778a5f7-94e1-4a0e-9e14-e4806e7e90a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " message: Optimization terminated successfully\n",
       " success: True\n",
       "  status: 0\n",
       "     fun: 2.0532077849552643\n",
       "       x: [ 1.263e-01  1.110e-01 ...  6.180e-02  3.301e-02]\n",
       "     nit: 30\n",
       "     jac: [-1.842e-02 -2.055e-03 ...  9.256e-03  0.000e+00]\n",
       "    nfev: 665\n",
       "    njev: 30"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "solution"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb3f88ac-356a-4d33-b53b-ed0d62da63f1",
   "metadata": {},
   "source": [
    "## 评价指标"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e267d54d-1bac-44fa-a473-2025bbd89abb",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_total_return(x):\n",
    "    # 计算收益率\n",
    "    days=len(close300)\n",
    "    total_return=0\n",
    "    daily_returns=[]\n",
    "    track_error=0\n",
    "    earning_rate=[]\n",
    "    for i in range(days):\n",
    "        if i==0:\n",
    "            continue\n",
    "        # 计算20支股票组合的当日收益率：(当日收盘价 - 前一日收盘价) / 前一日收盘价\n",
    "        earning_rate_i=0\n",
    "        date=close300.loc[i,'日期Date']\n",
    "        str_query='trade_date == ['+ str(date)+']'\n",
    "        df_i=closei.query(str_query)\n",
    "        df_i=df_i.reset_index()\n",
    "        for j in range(len(x)):\n",
    "            # 根据索引找到股票代码，然后计算当日收益率\n",
    "            item=df_i[df_i['ts_code']==index2codes[j]]\n",
    "            if not item.empty:\n",
    "                earning_rate_i_j=np.sum((item['close']-item['pre_close'])/item['pre_close'])\n",
    "            else:\n",
    "                # 这只股票当天没数据的话就等于0\n",
    "                earning_rate_i_j=0\n",
    "            earning_rate_i+=earning_rate_i_j*x[j]\n",
    "\n",
    "        # 找到当日沪深300的收益率\n",
    "        earning_rate300_i=(close300.loc[i,'收盘Close']-close300.loc[i-1,'收盘Close'])/close300.loc[i-1,'收盘Close']\n",
    "        # 当日跟踪误差误差(单位：1)\n",
    "        earning_rate.append(earning_rate_i-earning_rate300_i)\n",
    "        total_return+=earning_rate_i\n",
    "        daily_returns.append(earning_rate_i)\n",
    "    daily_returns=np.array(daily_returns)\n",
    "    # 求标准差\n",
    "    track_error=np.std(earning_rate)\n",
    "    return total_return,daily_returns,track_error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "9ece1bc5-b3a4-4794-a89d-8fe82c5a9d5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "eva_index={}\n",
    "ts_code_20=df20['ts_code'].tolist()\n",
    "close300=df300_daily[['日期Date','收盘Close']]\n",
    "index2codes=ts_code_20\n",
    "df2=pd.pivot_table(df,index=[\"trade_date\",\"ts_code\"],values=[\"close\",\"pre_close\"])\n",
    "closei=df2\n",
    "# 该组合跟踪误差\n",
    "total_return,daily_returns,track_error=get_total_return(x)\n",
    "eva_index['跟踪误差']=track_error\n",
    "\n",
    "# 年化收益率\n",
    "n=3 # 取的是近3年的数据\n",
    "annualized_return = (1 + total_return) ** (1 / n) - 1\n",
    "eva_index['年化收益率']=annualized_return\n",
    "\n",
    "# 年化波动率\n",
    "# 计算日波动率（日收益率的标准差）\n",
    "daily_volatility=np.std(daily_returns, ddof=1)\n",
    "# 将日波动率年化\n",
    "annualized_volatility=daily_volatility * np.sqrt(252)\n",
    "eva_index['年化波动率']=annualized_volatility\n",
    "\n",
    "# 夏普比率=（策略年化收益率 - 无风险年化收益率 ） / 策略年化波动率\n",
    "R_p = annualized_return  # 投资组合年化回报率\n",
    "R_f = 0.0125  # 无风险利率：3年期国债收益率\n",
    "sigma_p = annualized_volatility  # 投资组合的年化波动率\n",
    "sharpe_ratio = (R_p - R_f) / sigma_p\n",
    "eva_index['夏普比率']=sharpe_ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "9717f3b0-9f1a-4b3b-9c4d-40058c060cb3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'跟踪误差': 0.0037896002541941928,\n",
       " '年化收益率': 0.054033011105440565,\n",
       " '年化波动率': 0.17371873528856108,\n",
       " '夏普比率': 0.2390819334279105}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eva_index"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
